package com.sailing.codegenerate.database.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.ResourceBundle;

import com.sailing.codegenerate.database.utils.StringKits;

/**
 * 根据表名读取表信息构建TabInfos
 *
 * @author YW
 *
 */
public class TableReader {

	private static ResourceBundle config;
	private static String driverClassName;
	static {
		config = ResourceBundle.getBundle("freemarker//application-datasource");
		driverClassName = config.getString("spring.datasource.driver-class-name");
	}

	private Connection getConnection() throws SQLException {
		try {
			Class.forName(driverClassName);
		} catch (ClassNotFoundException e) {
			throw new SQLException("数据库驱动未加载...", e);
		}

		String url = config.getString("spring.datasource.url");
		String username = config.getString("spring.datasource.username");
		String password = config.getString("spring.datasource.password");

		return DriverManager.getConnection(url, username, password);
	}

	/**
	 * 根据实体名字获取数据库表字段信息
	 *
	 * @param tableName
	 * @return 表格信息
	 * @throws SQLException
	 */
	public TabInfos getTabInfos(String tableName, String pkIdFieldName) throws SQLException {
		TabInfos tabInfos = new TabInfos();
		String tableNameTemp = tableName;
		if (tableNameTemp.toLowerCase().startsWith("t_")) {
			tableNameTemp = tableNameTemp.replaceFirst("t_", "").replaceFirst("T_", "");
		}
		tabInfos.setEntityName(StringKits.underlineToHump(tableNameTemp, true));
		tabInfos.setTableName(tableName);
		tabInfos.setColums(getColumns(tableName, pkIdFieldName));
		tabInfos.setDescription(getTableDescription(tableName));
		return tabInfos;
	}

	private String getTableDescription(String tableName) throws SQLException {
		if (driverClassName.toLowerCase().contains("oracle")) {
			return getTableDescriptionForOracle(tableName);
		} else if ("org.postgresql.Driver".equals(driverClassName)) {
			return getTableDescriptionForPg(tableName);
		} else {
			throw new SQLException("不支持的数据库驱动：" + driverClassName);
		}
	}

	private String getTableDescriptionForPg(String tableName) throws SQLException {
		StringBuilder sql = new StringBuilder(
				"select cast(obj_description(relfilenode,'pg_class') as varchar) as table_comments from pg_class c where relname ='"
						+ tableName + "'");
		try (Connection connection = getConnection();
				PreparedStatement ps = connection.prepareStatement(sql.toString());) {
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				return rs.getString("table_comments");
			}
		} catch (SQLException e) {
			throw new SQLException("读取表字段出现错误", e);
		}
		return tableName;
	}

	private String getTableDescriptionForOracle(String tableName) {
		System.out.println("注意：oracle目前还没有实现获取表注释的方法，需要修改TableReader.getTableDescriptionForOracle代码实现");
		return tableName;
	}

	private List<Column> getColumns(String tableName, String pkIdFieldName) throws SQLException {
		if (driverClassName.toLowerCase().contains("oracle")) {
			return getColumnsForOracle(tableName, pkIdFieldName);
		} else if ("org.postgresql.Driver".equals(driverClassName)) {
			return getColumnsForPg(tableName, pkIdFieldName);
		} else {
			throw new SQLException("不支持的数据库驱动：" + driverClassName);
		}
	}

	private List<Column> getColumnsForOracle(String tableName, String pkIdFieldName) throws SQLException {
		StringBuilder sql = new StringBuilder("select b.column_name column_name,b.data_type data_type");
		sql.append(" ,b.data_length,b.nullable,a.comments comments");
		sql.append(" from USER_COL_COMMENTS a,ALL_TAB_COLUMNS b");
		sql.append(" where a.table_name = b.table_name and a.column_name = b.column_name and");
		sql.append(" a.table_name = '" + tableName + "' order by column_id");
		try (Connection connection = getConnection();
				PreparedStatement ps = connection.prepareStatement(sql.toString());) {
			ResultSet rs = ps.executeQuery();
			return getColumns(rs, pkIdFieldName);
		} catch (SQLException e) {
			throw new SQLException("读取表字段出现错误", e);
		}
	}

	private List<Column> getColumnsForPg(String tableName, String pkIdFieldName) throws SQLException {

		StringBuilder sql = new StringBuilder(
				"SELECT a.attname AS column_name, t.typname AS data_type,a.attlen AS data_length");
		sql.append(" , case when a.attnotnull='t' then 'false' else 'true' end AS nullable,b.description AS comments");
		sql.append(
				" FROM pg_class c,pg_attribute a LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid, pg_type t");
		sql.append(" WHERE c.relname = '" + tableName.toLowerCase()
				+ "' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid ORDER BY a.attnum;");
		try (Connection connection = getConnection();
				PreparedStatement ps = connection.prepareStatement(sql.toString());) {
			ResultSet rs = ps.executeQuery();
			return getColumns(rs, pkIdFieldName);
		} catch (SQLException e) {
			throw new SQLException("读取表字段出现错误", e);
		}
	}

	private List<Column> getColumns(ResultSet rs, String pkIdFieldName) throws SQLException {
		List<Column> columns = new ArrayList<Column>();
		while (rs.next()) {
			Column column = new Column(rs.getString("column_name"), rs.getString("data_type"), rs.getInt("data_length"),
					rs.getString("nullable"), rs.getString("comments"));
			if (column.getColumnName().equals(pkIdFieldName)) {
				column.setPkIdField(true);
			}
			columns.add(column);
		}
		return columns;
	}

	/**
	 * 只支持部分常用的数据类型转换
	 *
	 * @param clazz
	 * @return
	 */
	public boolean isBaseType(Class<?> clazz) {
		if (clazz == int.class || clazz == Integer.class) {
			return true;
		}
		if (clazz == double.class || clazz == Double.class) {
			return true;
		}
		if (clazz == float.class || clazz == Float.class) {
			return true;
		}
		if (clazz == long.class || clazz == Long.class) {
			return true;
		}
		if (clazz == Date.class) {
			return true;
		}
		if (clazz == String.class) {
			return true;
		}
		return false;
	}
}
